**************************************************************
*HURRICANES AND GAS GOUGING - COMPILE WHOLESALE RACK DATA  
**************************************************************
frame create wholesale
frame change wholesale

*****
*Orlando
import excel "$data_whole\Orlando-Tampa.xlsx", clear cellrange(A2:AXA1568) firstrow 
rename DateDaily date	
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor Orlando
	keep date `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="ORLANDO" 
gen state="FL"
sort city date
keep city state date whole* 
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Tampa
import excel "$data_whole\Orlando-Tampa.xlsx", clear cellrange(A2:AXA1568) firstrow 
rename DateDaily date	
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor Tampa
	keep date `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor hess 
	egen whole_hess=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="TAMPA" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Arcadia, LA   
import excel "$data_whole\UVA Arcadia.xlsx", clear cellrange(A2:JS1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
 qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="ARCADIA" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Archie, LA   
import excel "$data_whole\UVA Archie.xlsx", clear cellrange(A2:EB1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
gen city="ARCHIE" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Baton Rouge, LA   
import excel "$data_whole\UVA Baton Rouge.xlsx", clear cellrange(A2:ADB1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
 qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="BATON ROUGE" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Chalmette, LA   
import excel "$data_whole\UVA Chalmette.xlsx", clear cellrange(A2:IU1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="CHALMETTE" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Convent-Garyville, LA   
import excel "$data_whole\UVA Convent Garyville.xlsx", clear cellrange(A2:NS1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="CONVENT-GARYVILLE" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Fort Lauderdale
import excel "$data_whole\UVA Ft Lauderdale.xlsx", clear cellrange(A2:PS1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor hess 
	egen whole_hess=rowmean(`r(varlist)')
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')	
gen city="FORT LAUDERDALE" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Jacksonville, FL   
import excel "$data_whole\UVA Jax.xlsx", clear cellrange(A2:XA1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor hess 
	egen whole_hess=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="JACKSONVILLE" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Lake Charles, LA   
import excel "$data_whole\UVA Lake Charles.xlsx", clear cellrange(A2:SD1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')		
gen city="LAKE CHARLES" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Miami, FL   
import excel "$data_whole\UVA Miami.xlsx", clear cellrange(A2:AFQ1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor hess 
	egen whole_hess=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="MIAMI" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Monroe, LA   
import excel "$data_whole\UVA Monroe.xlsx", clear cellrange(A2:GS1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')		
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="MONROE" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*New Orleans, LA   
import excel "$data_whole\UVA New Orleans.xlsx", clear cellrange(A2:UP1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor marathon 
	egen whole_mar=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="NEW ORLEANS" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Niceville, FL   
import excel "$data_whole\UVA Niceville.xlsx", clear cellrange(A2:IV1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')		
qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="NICEVILLE" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Opelousas, LA   
import excel "$data_whole\UVA Opelousas.xlsx", clear cellrange(A2:KK1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="OPELOUSAS" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Panama City, FL   
import excel "$data_whole\UVA Panama City.xlsx", clear cellrange(A2:IB1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor chevron 
	egen whole_chev=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="PANAMA CITY" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Shreveport, LA   
import excel "$data_whole\UVA Shreveport.xlsx", clear cellrange(A2:NG1520) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor valero 
	egen whole_val=rowmean(`r(varlist)')	
gen city="SHREVEPORT" 
gen state="LA"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
save $data_clean\wholesale_clean_$outputdate, replace


*****
*Pensacola
import excel "$data_whole\UVA Pensacola.xlsx", clear cellrange(A2:IY1568) firstrow 
rename DateDaily date
qui lookfor opis shell citgo chevron bp mobil exxon sunoco hess texaco marathon ///
             murphy valero conoco phillips
	keep date `r(varlist)'
qui lookfor sulfur aviation ultra dye kerosene nrlm pure tex-shell ethanol ///
			low high mid premium DS jet
	drop `r(varlist)'
qui lookfor opis
	egen wholesale=rowmean(`r(varlist)')
qui lookfor shell 
	egen whole_shell=rowmean(`r(varlist)')
qui lookfor citgo 
	egen whole_citgo=rowmean(`r(varlist)')
qui lookfor bp 
	egen whole_bp=rowmean(`r(varlist)')
qui lookfor sunoco 
	egen whole_sun=rowmean(`r(varlist)')
qui lookfor texaco 
	egen whole_tex=rowmean(`r(varlist)')	
qui lookfor murphy 
	egen whole_murphy=rowmean(`r(varlist)')	
qui lookfor conoco phillips 
	egen whole_con=rowmean(`r(varlist)')	
gen city="PENSACOLA" 
gen state="FL"
sort city date
keep city state date whole* 
append using $data_clean\wholesale_clean_$outputdate
sort city date
order date city state
label var wholesale "Conv. Clear Reg (OPIS Avg)" 
label var whole_shell "Conv. Clear Reg (Shell)" 
label var whole_citgo "Conv. Clear Reg (Citgo)" 
label var whole_bp "Conv. Clear Reg (BP)" 
label var whole_sun "Conv. Clear Reg (Sunoco)" 
label var whole_tex "Conv. Clear Reg (Texaco)" 
label var whole_murphy "Conv. Clear Reg (Murphy)" 
label var whole_con "Conv. Clear Reg (Conoco-Philips)" 
label var whole_val "Conv. Clear Reg (Valero)" 
label var whole_chev "Conv. Clear Reg (Chevron)" 
label var whole_mar "Conv. Clear Reg (Marathon)" 
label var whole_hess "Conv. Clear Reg (Hess)" 
save $data_clean\wholesale_clean_$outputdate, replace


***************************************************
*Linking to Rack Location  
***************************************************
import delimited $data_whole\racks_Deanna.csv, clear 
keep rack_state rack_state_1 rack_latitude rack_longitude
duplicates drop
keep if rack_state_1=="FL"|rack_state_1=="LA"
rename rack_state city
	replace city="ARCADIA" if city=="Arcadia"
	replace city="ARCHIE" if city=="Archie"
	replace city="BATON ROUGE" if city=="Baton Rouge"
	replace city="CONVENT-GARYVILLE" if city=="Convent/Garyville"
	replace city="JACKSONVILLE" if city=="Jacksonville"
	replace city="LAKE CHARLES" if city=="Lake Charles"
	replace city="MIAMI" if city=="Miami"
	replace city="MONROE" if city=="Monroe"
	replace city="NEW ORLEANS" if city=="New Orleans"
	replace city="NICEVILLE" if city=="Niceville"
	replace city="OPELOUSAS" if city=="Opelousas"
	replace city="ORLANDO" if city=="Orlando"
	replace city="PANAMA CITY" if city=="Panama City"
	replace city="PENSACOLA" if city=="Pensacola"
	replace city="SHREVEPORT" if city=="Shreveport"
	replace city="TAMPA" if city=="Tampa"
rename rack_state_1 state

*Merging
merge 1:m city state using $data_clean\wholesale_clean_$outputdate
drop _merge

*Manually inputting Chalmette and Fort Lauderdale
replace rack_latitude=26.092785 if city=="FORT LAUDERDALE"
replace rack_longitude=-80.129924 if city=="FORT LAUDERDALE"
replace rack_latitude=29.935854 if city=="CHALMETTE"
replace rack_longitude=-89.967392 if city=="CHALMETTE"

*Generating rack id
encode city, gen(ct)
encode state, gen(st)
egen  rack_id=group(ct st)
drop ct st

save $data_clean\wholesale_clean_$outputdate, replace


*************************
*Counting number of observations for each price series
xtset rack_id date
tsfill 
gen tot0=1 
by rack_id: egen tot=sum(tot) 
drop tot0
gen miss=1 if !missing(wholesale)
by rack_id: egen count_wh=sum(miss)
by rack_id: gen frac_wh=count_wh/tot

foreach x in shell citgo bp sun tex murphy con val chev mar hess {
	gen miss_`x'=1 if !missing(whole_`x')
	by rack_id: egen count_`x'=sum(miss_`x')
	by rack_id: gen frac_`x'=count_`x'/tot
	replace whole_`x'=. if frac_`x'<0.75
	replace whole_`x'=wholesale if frac_`x'>=0.75 & missing(whole_`x')
 }
*

*************************
*Cleaning up wholesale racks

*Carryforward rack characteristics
gsort rack_id -city
by rack_id: carryforward city, replace
by rack_id: carryforward state, replace
by rack_id: carryforward rack_latitude, replace
by rack_id: carryforward rack_longitude, replace
sort rack_id date

*Replacing missing Sunday prices with Saturday prices
gen dow=dow(date)
xtset rack_id date 
by rack_id: replace wholesale=l.wholesale if dow==0
	
foreach x in shell citgo bp sun tex murphy con val chev mar hess {
	replace whole_`x'=l.whole_`x' if dow==0
 }
*
drop frac_* miss_* miss tot count_* dow

rename whole_chev whole_chevron
rename whole_con whole_conoco
rename whole_mar whole_marathon
rename whole_sun whole_sunoco
rename whole_tex whole_texaco
rename whole_val whole_valero
 
save $data_clean\wholesale_clean_$outputdate, replace

******
*Saving file with rack_id and lat/lon for distance calculations
keep rack_id rack_latitude rack_longitude
duplicates drop
gen id_dist=_n
	replace id_dist=id_dist+11603
save $data_clean\wholesale_loc_$outputdate, replace
